﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public sealed class HrmTrainingDAL
    { 
        public DataSet LoadTrainingCourses(Nullable<int> Sequence, bool Active)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@Active", Active);

                strSQL = "SELECT c.*"
                        + ", cr.TRAINING_CUR_DESCR"
                    + " FROM HRM_TRAINING_COURSES c "
                    + " LEFT JOIN HRM_TRAINING_CURRICULUMS cr ON c.TRAINING_CUR_SEQ = cr.TRAINING_CUR_SEQ"
                    + " WHERE c.RECORD_STATUS = @ActiveRecord"
                        + " AND c.CLOSED = @Active"
                    + (Sequence.HasValue ? " AND c.TRAIN_SEQ = @Sequence" : "");
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public DataSet LoadTrainingTrainee(Nullable<int> Sequence, Nullable<int> EmployeeSequence, Nullable<bool> Granted)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@EmployeeSequence", EmployeeSequence);
                dbManager.AddParameters(3, "@Granted", Granted);

                strSQL = "SELECT tn.*"
                        + ", c.TRAINING_COURSE_DESCR, c.DATE_TRAINING_START, c.DATE_TRAINING_END"
                        + ", CASE WHEN tn.GRANTED = 1 THEN 'มีสิทธิ์เข้าอบรม'"
                            + " ELSE 'ไม่มีสิทธิ์เข้าอบรม'"
                            + " END AS GRANTED_TEXT"
                        + ", ms.SEND_DATE"
                        + ", CASE WHEN rp.REPLY_TYPE = '0' THEN 'ไม่เห็นชอบ'"
                            + " ELSE CASE WHEN rp.REPLY_TYPE = '1' THEN 'เห็นชอบ'"
                                + " ELSE CASE WHEN rp.REPLY_TYPE = '2' THEN 'ส่งต่อ'"
                                    + " ELSE 'รอการพิจารณา'"
                                    + " END"
                                + " END"
                        + " END AS REPLY_TYPE_NAME"
                    + " FROM MESSAGE_SEND ms"
                    + " LEFT JOIN MESSAGE_REPLY rp ON ms.SEND_SEQ = rp.FROM_SEND_SEQ"
                    + " LEFT JOIN HRM_TRAINING_COURSES_TRAINEES tn ON ms.FROM_EMP_SEQ = tn.EMP_SEQ"
                    + " LEFT JOIN HRM_TRAINING_COURSES c ON ms.REF_SEQ = c.TRAIN_SEQ"
                    + " WHERE ms.RECORD_STATUS = @ActiveRecord"
                    + " AND ms.REF_TYPE = 'hrm_training_requests'"
                    //+ (Sequence.HasValue ? " AND tn.TRAINEE_SEQ = @Sequence" : "")
                    + (Granted.HasValue ? " AND tn.GRANTED = @Granted" : "")
                    + (EmployeeSequence.HasValue ? " AND ms.FROM_EMP_SEQ = @EmployeeSequence" : "");
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public void InsertTrainingTrainee(int EmployeeSequence, int CoursesSequence, int CreatedBy)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@EmployeeSequence", EmployeeSequence);
                dbManager.AddParameters(1, "@CoursesSequence", CoursesSequence);
                dbManager.AddParameters(2, "@Granted", 0);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(5, "@CreatedWhen", DateTime.Now);

                strSQL = "INSERT INTO HRM_TRAINING_COURSES_TRAINEES"
                    + "(TRAIN_SEQ, EMP_SEQ, GRANTED, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + "VALUES(@CoursesSequence, @EmployeeSequence, @Granted, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        //public void UpdateTrainingRequests(TR_TrainingRequestInfo info)
        //{
        //    string strSQL;
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(4);

        //        dbManager.AddParameters(0, "@request_seq", info.request_seq);
        //        dbManager.AddParameters(1, "@updated_by", info.updated_by);
        //        dbManager.AddParameters(2, "@updated_when", info.updated_when);
        //        dbManager.AddParameters(3, "@status_seq", info.status_seq);

        //        strSQL = "UPDATE hrm_training_request tr"
        //                + " SET tr.status_seq = @status_seq"
        //                + ", tr.updated_by = @updated_by"
        //                + ", updated_when = @updated_when"
        //                + " WHERE tr.request_seq = @request_seq";

        //        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}
    }
}
